<?php
/**
 * Joomla! 1.5 component ArticleCashRewards
 *
 * @version $Id: userearnings.php 2009-05-10 01:47:01 svn $
 * @author Maverick
 * @package models
 * @subpackage ArticleCashRewards
 * @license GNU/GPL
 * Website: http://www.boddunan.com
 *
 * Review and approve the articles and assign points and cash rewards.
 * Users can see their articles and the points/cash rewards assigned in the front-end module.
 */

// no direct access
defined('_JEXEC') or die('Restricted access');

// Import Joomla! libraries
jimport('joomla.application.component.model');

if(!class_exists('ContentHelperRoute')) require_once (JPATH_SITE . '/components/com_content/helpers/route.php');  

class ArticleCashRewardsModelReports extends JModel {

	var $_total = null; 
	var $_pagination = null;
	
	function __construct(){
	
		parent::__construct();
		global $mainframe, $option;
	
		// Get pagination request variables
		$limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		$limitstart = $mainframe->getUserStateFromRequest($option.'.limitstart', 'limitstart', 0, 'int');
		
		// In case limit has been changed, adjust it
		$limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
		
		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);
	}
	    
    function getEarningsReport() {
    	
    	global $mainframe, $option, $acr_config;
    	// Get a database object
		$db =& JFactory::getDBO();
		$user =& JFactory::getUser();
		$filter_order		= $mainframe->getUserStateFromRequest( "$option.filter_order",		'filter_order',		'u.username',	'cmd' );
		$filter_order_Dir	= $mainframe->getUserStateFromRequest( "$option.filter_order_Dir",	'filter_order_Dir',	'ASC',		'word' );
		
		$orderby 	= ' ORDER BY '. $filter_order .' '. $filter_order_Dir;

		// Get the total number of records
		$query = 'SELECT COUNT(*)' .
				' FROM #__content AS c' .
				' WHERE c.state = 0';

		$db->setQuery($query);
		$total = $db->loadResult();
		
		$query = 'SELECT au.id AS author_id, u.username AS author, au.points AS points,'.
            ' au.cash AS cash, au.paid AS paid_cash, au.redeamed AS redeamed_points,'.
            ' (SELECT CASE au.cash_threshold WHEN 0.00 THEN ' . $acr_config[CONFIG_MIN_PAYMENT_THRESHOLD] .
            ' ELSE au.cash_threshold END) AS cash_threshold, (au.cash - au.paid) AS pending_cash,'.
            ' (au.points - au.redeamed) AS pending_points'.
            ' FROM ' . TABLE_ACR_USERS  . ' au LEFT JOIN #__users u ON au.id = u.id' . 
            ' ORDER BY au.cash DESC';
//echo $query;
		$db->setQuery($query, $this->getState('limitstart'), $this->getState('limit'));
		$rows = $db->loadObjectList();

		$lists['order_Dir']	= $filter_order_Dir;
		$lists['order']		= $filter_order;
		$result->rows = $rows;
		$result->lists = $lists;
		
		return $result;
    }

    function syncAup(){
        global $acr_config;
        $db =& JFactory::getDBO();
        $query = 'SELECT c.created_by as author, ac.article_id, c.title AS article, ac.points FROM ' . TABLE_ACR_CONTENT .
            ' ac LEFT JOIN #__content c ON ac.article_id=c.id';
        $db->setQuery($query);
        if(strcasecmp($acr_config[CONFIG_POINTS_SYSTEM],POINTS_SYSTEM_AUP) == 0){
            $rows = $db->loadObjectList();
            foreach($rows as $item){
                $api_AUP = JPATH_SITE.DS.'components'.DS.'com_alphauserpoints'.DS.'helper.php';
                if ( file_exists($api_AUP)){
                    require_once ($api_AUP);
                    $aupid = AlphaUserPointsHelper::getAnyUserReferreID( $item->author );
                    if($aupid){
                        AlphaUserPointsHelper::newpoints( POINTS_SYSTEM_AUP_PLUGIN, $aupid,
                            'ACRSYNC-' . $item->article_id, 'Article: ' . $item->article, $item->points, true);
                    }
                }
            }
        }
    }

    function getPaymentReport($status) {

    	global $mainframe, $option, $acr_config;
    	// Get a database object
		$db =& JFactory::getDBO();

        $query = 'SELECT ap.id, ap.invoice_number, ap.payee_id, u.username, ap.payee_name,' .
            ' ap.amount_paid, ap.mailing_address, ap.date_paid, ap.admin_uid, au.paid,' .
            ' au.cash, ap.payment_method, ap.remarks, au.security_code FROM ' .
            TABLE_ACR_PAYMENTS . ' ap LEFT JOIN #__users u ON ap.payee_id=u.id LEFT JOIN ' . 
            TABLE_ACR_USERS . ' au ON ap.payee_id=au.id WHERE status=' . $status;

		$db->setQuery($query, $this->getState('limitstart'), $this->getState('limit'));
		$rows = $db->loadObjectList();
        
        return $rows;
    }


    function runPaymentReports(){
        global $mainframe, $option, $acr_config;
        $db =& JFactory::getDBO();
		$user =& JFactory::getUser();

        $year = date('Y');
        
        $query = 'INSERT INTO ' . TABLE_ACR_PAYMENTS . ' (invoice_number, payee_id,'.
                ' payee_name, amount_paid, mailing_address, date_paid, status, admin_uid,'.
                ' payment_method, remarks) SELECT \'BN-' . $year . '-\', au.id, au.payee_name,'.
                ' au.cash-au.paid, au.mailing_address, NOW(), 0, ' . $user->id . ', \'' .
                $acr_config[CONFIG_DEFAULT_PAY_METHOD] . '\', \'\' FROM ' . TABLE_ACR_USERS .
                ' au WHERE au.cash-au.paid >= ' . $acr_config[CONFIG_MIN_PAYMENT_THRESHOLD] .
                ' AND (SELECT COUNT(*) FROM ' . TABLE_ACR_PAYMENTS . ' t WHERE t.payee_id=au.id' .
                ' AND t.status=' . STATUS_PENDING . ') = 0';
//echo $query;
        $db->setQuery($query);
        if($db->query()){
            return true;
        }else{
            return false;
        }
    }

    function processPayments($cids){
    	global $mainframe, $option, $acr_config;
    	// Get a database object
		$db =& JFactory::getDBO();
		$user =& JFactory::getUser();

        $query = 'SELECT id, payee_id, amount_paid FROM ' . TABLE_ACR_PAYMENTS . ' WHERE id IN(' .
            implode(',', $cids) . ')';
        $db->setQuery($query);
        $payments = $db->loadObjectList();
        
        foreach($payments as $payment){
            
            $query = 'UPDATE ' . TABLE_ACR_USERS . ' SET paid=paid+' . $payment->amount_paid .
                ' WHERE id=' . $payment->payee_id;
            $db->setQuery($query);
            
            if($db->query()){
                $query = 'UPDATE ' . TABLE_ACR_PAYMENTS . ' SET status=' . STATUS_APPROVED .
                    ' WHERE id=' . $payment->id;
                $db->setQuery($query);
                $db->query();
            }

            $helper = JPATH_ADMINISTRATOR.DS.'components'.DS.'com_articlecashrewards'.DS.'helpers'.DS.'helper.php';
            if ( file_exists($helper)){
                require_once ($helper);
                $sec_code = ArticlecashrewardsHelper::generatePassword(10);
                $query = 'UPDATE ' . TABLE_ACR_USERS . ' SET security_code="' . $sec_code . '"' .
                    ' WHERE (security_code IS NULL OR (security_code != 1 OR security_code != 2)) AND id=' . $payment->payee_id;
//echo $query;
                $db->setQuery($query);
                $db->query();
            }
        }
        return true;
    }

    function getTotal($page){

        $db =& JFactory::getDBO();
        switch ($page){
            case 1:
                $query = 'SELECT COUNT(au.id) FROM ' . TABLE_ACR_USERS  . ' au';
                $db->setQuery( $query );
                $this->_total = $db->loadResult();
            case 2:
                $query = 'SELECT COUNT(*) FROM ' . TABLE_ACR_PAYMENTS . ' WHERE status=0';
                $db->setQuery( $query );
                $this->_total = $db->loadResult();
            case 3:
                $query = 'SELECT COUNT(*) FROM ' . TABLE_ACR_PAYMENTS . ' WHERE status=1';
                $db->setQuery( $query );
                $this->_total = $db->loadResult();
        }
		return $this->_total;
	}
	  
	function getPagination($page){
		$pnls = JRequest::getVar( 'pnlimitstart', 'null');
		
		if($pnls == 'null'){
		    jimport('joomla.html.pagination');
		    $this->_pagination = new JPagination($this->getTotal($page), 0, $this->getState('limit') );
		    $this->setState('limitstart', 0);
		}

		// Load the content if it doesn't already exist
		if (empty($this->_pagination)) {
		    jimport('joomla.html.pagination');
		    $this->_pagination = new JPagination($this->getTotal($page), $this->getState('limitstart'), $this->getState('limit') );
		}

		return $this->_pagination;
	}

    function recalculateEarnings($cids){
        $db =& JFactory::getDBO();

        $where_c = '';
        $where_wl = '';
        if(null != $cids && count($cids) > 0){
            $uids = implode(',', $cids);

            $where = ' WHERE id IN (' . $uids . ')';

            $where_c = 'WHERE a.created_by IN (' . $uids . ') AND acr.status=' . STATUS_APPROVED;

            $where_wl = 'WHERE aw.userid IN (' . $uids . ') AND aw.status=' . STATUS_APPROVED;

            $where_ref = 'WHERE ar.referredby IN (' . $uids . ') AND ar.status=' . STATUS_APPROVED;

            $where_award = 'WHERE aa.author IN (' . $uids . ') AND aa.status=' . STATUS_APPROVED;
        }
        $query = 'UPDATE ' . TABLE_ACR_USERS . ' SET cash=0' . $where;
        $db->setQuery( $query );
        if (!$db->query()) {
            JError::raiseError( 500, $db->getErrorMsg() );
            return false;
        }

        // Articles
        $query = 'INSERT INTO ' . TABLE_ACR_USERS  . '(id, points, cash, articles_cash)'.
            ' SELECT t.id, t.tot_points, t.tot_cash, t.tot_cash as articles_cash  FROM'.
            ' (SELECT a.created_by as id, SUM(acr.points) as tot_points,'.
            ' SUM(acr.cash) AS tot_cash FROM ' . TABLE_ACR_CONTENT  .
            ' acr LEFT JOIN #__content a ON acr.article_id = a.id ' .
            $where_c . ' GROUP BY a.created_by) t ON DUPLICATE KEY UPDATE' .
            ' points=t.tot_points, cash=t.tot_cash, articles_cash=t.tot_cash;';
        $db->setQuery( $query );

        if (!$db->query()) {
			JError::raiseError( 500, $db->getErrorMsg() );
			return false;
		}

        //Weblinks
        $query = 'INSERT INTO ' . TABLE_ACR_USERS  . '(id, cash, weblinks_cash)'.
            ' SELECT t.userid, t.tot_cash, t.tot_cash as weblinks_cash  FROM'.
            '(SELECT aw.userid, SUM(aw.cash_rewards) as tot_cash,  FROM ' .
            TABLE_ACR_WEBLINKS . ' aw ' . $where_wl . ' GROUP BY aw.userid) t' .
            'ON DUPLICATE KEY UPDATE cash=cash+t.tot_cash, weblinks_cash=t.tot_cash;';
        $db->setQuery($query);

        if (!$db->query()) {
			JError::raiseError( 500, $db->getErrorMsg() );
			return false;
		}

        // Referrals
        $query = 'INSERT INTO ' . TABLE_ACR_USERS  . '(id, cash, referrals_cash)'.
            ' SELECT t.id, t.tot_cash, t.tot_cash as referrals_cash  FROM' .
            ' (SELECT ar.referredby as id, SUM(ar.amount) as tot_cash'.
            ' FROM ' . TABLE_ACR_REFERRALS . ' ar ' . $where_ref . ' GROUP BY ar.referredby) t' .
            ' ON DUPLICATE KEY UPDATE cash=cash+t.tot_cash, referrals_cash=t.tot_cash;';
        $db->setQuery($query);

        if (!$db->query()) {
			JError::raiseError( 500, $db->getErrorMsg() );
			return false;
		}

        // Awards
        $query = 'INSERT INTO ' . TABLE_ACR_USERS  . '(id, cash, awards_cash)'.
            ' SELECT t.id, t.tot_cash, t.tot_cash AS awards_cash  FROM' .
            ' (SELECT aa.author AS id, SUM(aa.amount) as tot_cash FROM ' .
            TABLE_ACR_ADHOC . ' aa ' . $where_award . ' GROUP BY aa.author) t' .
            ' ON DUPLICATE KEY UPDATE cash=cash+t.tot_cash, awards_cash=t.awards_cash;';
        $db->setQuery($query);

        if (!$db->query()) {
			JError::raiseError( 500, $db->getErrorMsg() );
			return false;
		}
        return true;
    }

    function getUserIds($cid, $model){

        $db =& JFactory::getDBO();
        $cids = implode(',', $cids);
        $query = '';
        switch ($model){
            case 1: //Web Links
                $query = 'SELECT DISTINT(userid) FROM ' . TABLE_ACR_WEBLINKS .
                    ' WHERE id IN (' . $cids . ')';
                break;

            case 2: //Referrals
                $query = 'SELECT DISTINT(referredby) FROM ' . TABLE_ACR_WEBLINKS .
                    ' WHERE id IN (' . $cids . ')';
                break;
        }

        $db->setQuery($query);
        $rows = $db->loadResultArray();

        return $rows;
    }
}
?>